package com.amex.SQL;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.amex.uam.pojo.Country;
import com.amex.uam.pojo.Region;

public class DBViewUserProfileServiceHandler {
	
	private static String USER_ADMIN_STA_CD = "select distinct USER_ADMIN_STA_CD from MS_USER_ADMIN";
	
	public static List<String> getUserAdminSTACD(){
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<String> cdList = new ArrayList<String>();
		if(conn==null){
			return null;
		}
		try {
			pstat = conn.prepareStatement(USER_ADMIN_STA_CD);
			rs = pstat.executeQuery();
			
			while(rs.next()){
				cdList.add(rs.getString(rs.getMetaData().getColumnName(1)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBOperations.disconnect(conn);
		}
		return cdList;
	}
	
	private static String COUNTRY_ID_NAME = "select C.IDENTIFIER_ID, C.NAME_NM from COUNTRY C";
	public static List<Country> getCountryIdName(){
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Country> countryList = new ArrayList<Country>();
		if(conn==null){
			return null;
		}
		try {
			pstat = conn.prepareStatement(COUNTRY_ID_NAME);
			rs = pstat.executeQuery();
			
			while(rs.next()){
				Country c = new Country();
				c.setIDENTIFIER_ID(rs.getString("IDENTIFIER_ID"));
				c.setNAME_NM(rs.getString("NAME_NM"));
				countryList.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBOperations.disconnect(conn);
		}
		return countryList;
	}
	private static String REGION_ID_NAME = "select RGN_ID, NM_TX from REGION R where R.FK_CTRYCTRY_ID = ?";
	public static List<Region> getRegionIdName(String countryId){
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Region> regionList = new ArrayList<Region>();
		if(conn==null){
			return null;
		}
		try {
			pstat = conn.prepareStatement(REGION_ID_NAME);
			pstat.setString(1, countryId);
			rs = pstat.executeQuery();
			
			while(rs.next()){
				Region r = new Region();
				r.setRGN_ID(rs.getString("RGN_ID"));
				r.setNM_TX(rs.getString("NM_TX"));
				regionList.add(r);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBOperations.disconnect(conn);
		}
		return regionList;
	}
	
	private static String UPDATE_ADMIN_CODE ="UPDATE MS_USER_ADMIN "
			+ "SET USER_ADMIN_STA_CD=?, LST_UPDT_TS=CURRENT_TIMESTAMP "
			+ "WHERE WORKFORCE_MBR_ID = ?";
	
	public boolean updateAdminCode(String WORKFORCE_MBR_ID, String ADMIN_CODE){
		if(WORKFORCE_MBR_ID==null || WORKFORCE_MBR_ID.equalsIgnoreCase("") || WORKFORCE_MBR_ID.equalsIgnoreCase(" ")
				||ADMIN_CODE==null || ADMIN_CODE.equalsIgnoreCase("")||ADMIN_CODE.equalsIgnoreCase(" ")){
			return false;
		}
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		if(conn==null){
			return false;
		}
		try {
			pstat=conn.prepareStatement(UPDATE_ADMIN_CODE);
			pstat.setString(1, WORKFORCE_MBR_ID);
			pstat.setString(2, ADMIN_CODE);
			int rowCount = pstat.executeUpdate();
			return (rowCount > 0);
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}finally{
			DBOperations.disconnect(conn, pstat);
		}
	}
	
	private static String VUP_AMEX_BUS = "SELECT AMEX_BUS_CD FROM AMEX_BUSINESS WHERE AMEX_BUS_CD=?";
	public boolean insertAmexBus(String AMEX_BUS_CD, String AMEX_BUS_DS_TX){
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		if(conn==null){
			return false;
		}
		try {
			pstat = conn.prepareStatement(VUP_AMEX_BUS);
			pstat.setString(1, AMEX_BUS_CD);
			rs = pstat.executeQuery();
			if(!rs.next()){
				String insertAmexBus = "INSERT INTO AMEX_BUSINESS(AMEX_BUS_CD, AMEX_BUS_DS_TX) VALUES(?, ?)";
				pstat = conn.prepareStatement(insertAmexBus);
				pstat.setString(1, AMEX_BUS_CD);
				pstat.setString(2, AMEX_BUS_DS_TX);
				int rowCount = pstat.executeUpdate();
				return (rowCount > 0);
			}
			return true;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}finally{
			DBOperations.disconnect(conn, rs);
		}
	}
	
	private static String VUP_AMEX_UNIT = "SELECT AMEX_UNIT_CD FROM AMEX_UNIT WHERE AMEX_UNIT_CD=?";
	public boolean insertAmexUnitCd(String AMEX_UNIT_CD, String AMEX_BUS_DS_TX){
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		if(conn==null){
			return false;
		}
		try {
			pstat = conn.prepareStatement(VUP_AMEX_UNIT);
			pstat.setString(1, AMEX_UNIT_CD);
			rs = pstat.executeQuery();
			if(!rs.next()){
				String insertAmexUnit = "INSERT INTO AMEX_UNIT(AMEX_UNIT_CD, AMEX_UNIT_DS_TX) VALUES(?, ?)";
				pstat = conn.prepareStatement(insertAmexUnit);
				pstat.setString(1, AMEX_UNIT_CD);
				pstat.setString(2, AMEX_BUS_DS_TX);
				int rowCount = pstat.executeUpdate();
				return (rowCount > 0);
			}
			return true;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}finally{
			DBOperations.disconnect(conn, rs);
		}
	}
	
	private static String VUP_DEPT = "DELETE FROM USER_DEPT WHERE DEPT_ID = ?";
	public boolean insertDeptId(String DEPT_ID, String DEPT_NM){
		if(DEPT_ID==null){
			return false;
		}
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		if(conn==null){
			return false;
		}
		try {
			pstat = conn.prepareStatement(VUP_DEPT);
			pstat.setString(1, DEPT_ID);
			int rowCount = pstat.executeUpdate();
			if(rowCount>=0){
				String insertDeptId = "INSERT INTO USER_DEPT(DEPT_ID, DEPT_NM) VALUES(?,?)";
				pstat = conn.prepareStatement(insertDeptId);
				pstat.setString(1, DEPT_ID);
				pstat.setString(2, DEPT_NM);
				int result = pstat.executeUpdate();
				return (result>0);
			}
			return false;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}finally{
			DBOperations.disconnect(conn);
		}
	}
	
	private static String VUP_CLM = "DELETE FROM MS_USER_CLM_ACS WHERE WORKFORCE_MBR_ID = ?";
	public boolean insertCLM(String WORKFORCE_MBR_ID, String POA_SALE_SERV_ID){
		if(WORKFORCE_MBR_ID==null || WORKFORCE_MBR_ID.equalsIgnoreCase("") || WORKFORCE_MBR_ID.equalsIgnoreCase(" ")){
			return false;
		}
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		if(conn==null){
			return false;
		}
		try {
			pstat = conn.prepareStatement(VUP_CLM);
			pstat.setString(1, WORKFORCE_MBR_ID);
			int rowCount = pstat.executeUpdate();
			if(rowCount>=0){
				String insertCLM = "INSERT INTO MS_USER_CLM_ACS (WORKFORCE_MBR_ID, POA_SALE_SERV_ID, CLM_DLGTE_IN, "
						+ "LST_UPDT_DT, DLGTE_REQ_STA_CD) VALUES (?,?,'N',"+new Timestamp(System.currentTimeMillis())+", 'A')";
				pstat = conn.prepareStatement(insertCLM);
				pstat.setString(1, WORKFORCE_MBR_ID);
				pstat.setString(2, POA_SALE_SERV_ID);
				int result = pstat.executeUpdate();
				return (result>0);
			}
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}finally{
			DBOperations.disconnect(conn);
		}
	}
	
	private static String VUP_LIF_GOVERNANCE = "DELETE FROM MS_USER_GEO_AREA_SECR_FLTR WHERE WORKFORCE_MBR_ID = ?";
	public boolean insertLifGovn(String WORKFORCE_MBR_ID, String GEO_AREA_ID){
		if(WORKFORCE_MBR_ID==null || WORKFORCE_MBR_ID.equalsIgnoreCase("") || WORKFORCE_MBR_ID.equalsIgnoreCase(" ")){
			return false;
		}
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		if(conn==null){
			return false;
		}
		try {
			pstat = conn.prepareStatement(VUP_LIF_GOVERNANCE);
			pstat.setString(1, WORKFORCE_MBR_ID);
			int rowCount = pstat.executeUpdate();
			if(rowCount>=0){
				String insertCLM = "INSERT INTO MS_USER_GEO_AREA_SECR_FLTR (WORKFORCE_MBR_ID, GEO_AREA_ID,USER_DATA_FLTR_TYPE_CD,"
						+ "CREAT_TS) VALUES (?,?, 'LIF',"+new Timestamp(System.currentTimeMillis())+", 'A')";
				pstat = conn.prepareStatement(insertCLM);
				pstat.setString(1, WORKFORCE_MBR_ID);
				pstat.setString(2, GEO_AREA_ID);
				int result = pstat.executeUpdate();
				return (result>0);
			}
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}finally{
			DBOperations.disconnect(conn);
		}
	}
	
	private static String VUP_MARC_GEOGRAPHY = "DELETE FROM MS_USER_RGN_ACSS WHERE WORKFORCE_MBR_ID = ? AND RGN_ABBR_NM = ?";
	public boolean insertMARCGEO(String WORKFORCE_MBR_ID, String RGN_ABBR_NM){
		if(WORKFORCE_MBR_ID==null || WORKFORCE_MBR_ID.equalsIgnoreCase("") || WORKFORCE_MBR_ID.equalsIgnoreCase(" ")){
			return false;
		}
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		if(conn==null){
			return false;
		}
		try {
			pstat = conn.prepareStatement(VUP_MARC_GEOGRAPHY);
			pstat.setString(1, WORKFORCE_MBR_ID);
			pstat.setString(2, RGN_ABBR_NM);
			int rowCount = pstat.executeUpdate();
			if(rowCount>=0){
				String insertCLM = "INSERT INTO MS_USER_RGN_ACSS (WORKFORCE_MBR_ID, RGN_ABBR_NM,CREAT_TS) "
						+ "VALUES (?,? ,"+new Timestamp(System.currentTimeMillis())+")";
				pstat = conn.prepareStatement(insertCLM);
				pstat.setString(1, WORKFORCE_MBR_ID);
				pstat.setString(2, RGN_ABBR_NM);
				int result = pstat.executeUpdate();
				return (result>0);
			}
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}finally{
			DBOperations.disconnect(conn);
		}
	}
	
	public static void main(String args[]){
		/*List<String> resultCD = getUserAdminSTACD();
		if(resultCD==null){
			System.out.println("resultCD is null !");
		}else{
			for(int i = 0; i < resultCD.size(); i++){
				System.out.println(resultCD.get(i));
			}
		}*/
		/*List<Country> countryList = getCountryIdName();
		for(int i = 0; i<countryList.size(); i++){
			Country c = countryList.get(i);
			System.out.print(c.getIDENTIFIER_ID());
			System.out.println(c.getNAME_NM());
		}*/
		List<Region> regionList = getRegionIdName("008");
		for(int i = 0; i<regionList.size(); i++){
			Region r = regionList.get(i);
			System.out.print(r.getRGN_ID());
			System.out.println(r.getNM_TX());
		}
		
	}
}
